package com.hnist.empdemo.mapper.Impl;

import com.hnist.empdemo.common.Config;
import com.hnist.empdemo.pojo.Employee;
import java.sql.SQLException;

public class employMapperImpl {

    private static employMapperImpl employMapper = new employMapperImpl();

    private employMapperImpl() {
    }

    public static employMapperImpl getInstance() {
        return employMapper;
    }

    /**
     * 使用JDBC连接MySQL数据库的工具方法
     *
     * @param url 数据库连接URL，格式：jdbc:mysql://主机名:端口/数据库名
     * @param username 数据库用户名
     * @param password 数据库密码
     * @return 返回数据库连接对象，成功则返回Connection实例，失败返回null
     * @throws SQLException 当数据库连接失败时抛出异常
     */
    public static java.sql.Connection getConnection(String url, String username, String password) throws java.sql.SQLException {
        // 定义数据库驱动类名
        String driver = "com.mysql.cj.jdbc.Driver";

        // 加载MySQL驱动程序
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.err.println("MySQL驱动加载失败：" + e.getMessage());
            throw new RuntimeException("MySQL驱动未找到", e);
        }

        // 创建数据库连接
        java.sql.Connection connection = null;
        try {
            connection = java.sql.DriverManager.getConnection(url, username, password);
            System.out.println("数据库连接成功！");
        } catch (java.sql.SQLException e) {
            System.err.println("数据库连接失败：" + e.getMessage());
            throw e;
        }

        return connection;
    }

    public static void closeConnection(java.sql.Connection connection) {
        if (connection != null) {
            try {
                connection.close();
                System.out.println("数据库连接已关闭！");
            } catch (java.sql.SQLException e) {
                System.err.println("数据库连接关闭失败：" + e.getMessage());
            }
        }
    }

    /**
     * 根据员工信息查询数据库获取完整员工数据列表
     *
     * @param employee 包含查询条件的员工对象
     * @return 查询到的员工对象列表，如果没有找到返回空列表
     * @throws SQLException 数据库操作异常
     */
    public java.util.List<Employee> queryEmpInfo(Employee employee) {
        java.sql.Connection conn = null;
        java.sql.PreparedStatement stmt = null;
        java.sql.ResultSet rs = null;
        java.util.List<Employee> result = new java.util.ArrayList<>();

        try {
            // 获取数据库连接
            conn = getConnection(Config.JDBC_URL, Config.JDBC_USERNAME, Config.JDBC_PASSWORD);

            // 构建SQL查询语句
            String sql = "SELECT id, name, gender, department, position, salary, bonus FROM employee WHERE 1=1";
            java.util.List<String> conditions = new java.util.ArrayList<>();

            // 添加查询条件
            if (employee.getId() != 0) {
                conditions.add("id = ?");
            }
            if (employee.getName() != null && !employee.getName().isEmpty()) {
                conditions.add("name LIKE ?");
            }
            if (employee.getGender() != null && !employee.getGender().isEmpty()) {
                conditions.add("gender = ?");
            }
            if (employee.getDepartment() != null && !employee.getDepartment().isEmpty()) {
                conditions.add("department = ?");
            }
            if (employee.getPosition() != null && !employee.getPosition().isEmpty()) {
                conditions.add("position = ?");
            }

            // 组装完整的SQL语句
            if (!conditions.isEmpty()) {
                sql += " AND " + String.join(" AND ", conditions);
            }

            // 准备执行SQL
            stmt = conn.prepareStatement(sql);

            // 设置参数
            int paramIndex = 1;
            if (employee.getId() != 0) {
                stmt.setInt(paramIndex++, employee.getId());
            }
            if (employee.getName() != null && !employee.getName().isEmpty()) {
                stmt.setString(paramIndex++, "%" + employee.getName() + "%");
            }
            if (employee.getGender() != null && !employee.getGender().isEmpty()) {
                stmt.setString(paramIndex++, employee.getGender());
            }
            if (employee.getDepartment() != null && !employee.getDepartment().isEmpty()) {
                stmt.setString(paramIndex++, employee.getDepartment());
            }
            if (employee.getPosition() != null && !employee.getPosition().isEmpty()) {
                stmt.setString(paramIndex++, employee.getPosition());
            }

            // 执行查询
            rs = stmt.executeQuery();

            // 处理结果集
            while (rs.next()) {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setGender(rs.getString("gender"));
                emp.setDepartment(rs.getString("department"));
                emp.setPosition(rs.getString("position"));
                emp.setSalary(rs.getDouble("salary"));
                emp.setBonus(rs.getDouble("bonus"));
                result.add(emp);
            }

        } catch (java.sql.SQLException e) {
            System.err.println("查询员工信息失败：" + e.getMessage());
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (java.sql.SQLException e) {
                System.err.println("关闭数据库连接失败：" + e.getMessage());
            }
        }
        return result;
    }



    /**
     * 添加员工信息到数据库
     *
     * @param employee 要添加的员工对象
     * @throws SQLException 数据库操作异常
     */
    public Boolean addEmployee(Employee employee) {
        java.sql.Connection conn = null;
        java.sql.PreparedStatement stmt = null;

        try {
            // 获取数据库连接
            conn = getConnection(Config.JDBC_URL, Config.JDBC_USERNAME, Config.JDBC_PASSWORD);

            // 构建插入SQL语句
            String sql = "INSERT INTO employee (id, name, gender, department, position, salary, bonus) VALUES (?, ?, ?, ?, ?, ?, ?)";

            // 准备执行SQL
            stmt = conn.prepareStatement(sql);

            // 设置参数
            stmt.setInt(1, employee.getId());
            stmt.setString(2, employee.getName());
            stmt.setString(3, employee.getGender());
            stmt.setString(4, employee.getDepartment());
            stmt.setString(5, employee.getPosition());
            stmt.setDouble(6, employee.getSalary());
            stmt.setDouble(7, employee.getBonus());

            // 执行插入操作
            int rowsAffected = stmt.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println("员工信息添加成功！");
                return true;
            } else {
                System.out.println("员工信息添加失败！");
                return false;
            }

        } catch (java.sql.SQLException e) {
            System.err.println("添加员工信息失败：" + e.getMessage());
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (java.sql.SQLException e) {
                System.err.println("关闭数据库连接失败：" + e.getMessage());
            }
        }
        return false;
    }

    /**
     * 修改员工信息到数据库
     *
     * @param employee 要修改的员工对象，包含新的员工信息
     * @throws SQLException 数据库操作异常
     */
    public Boolean updateEmployee(Employee employee) {
        java.sql.Connection conn = null;
        java.sql.PreparedStatement stmt = null;

        try {
            // 获取数据库连接
            conn = getConnection(Config.JDBC_URL, Config.JDBC_USERNAME, Config.JDBC_PASSWORD);

            // 构建更新SQL语句
            String sql = "UPDATE employee SET name = ?, gender = ?, department = ?, position = ?, salary = ?, bonus = ? WHERE id = ?";

            // 准备执行SQL
            stmt = conn.prepareStatement(sql);

            // 设置参数
            stmt.setString(1, employee.getName());
            stmt.setString(2, employee.getGender());
            stmt.setString(3, employee.getDepartment());
            stmt.setString(4, employee.getPosition());
            stmt.setDouble(5, employee.getSalary());
            stmt.setDouble(6, employee.getBonus());
            stmt.setInt(7, employee.getId());

            // 执行更新操作
            int rowsAffected = stmt.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println("员工信息修改成功！");
                return true;
            } else {
                System.out.println("员工信息修改失败！可能原因：员工不存在或没有修改任何字段。");
                return false;
            }

        } catch (java.sql.SQLException e) {
            System.err.println("修改员工信息失败：" + e.getMessage());
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (java.sql.SQLException e) {
                System.err.println("关闭数据库连接失败：" + e.getMessage());
            }
        }
        return false;
    }


    /**
     * 删除员工信息从数据库
     *
     * @param id 要删除的员工编号
     * @throws SQLException 数据库操作异常
     */
    public Boolean deleteEmployeeById(int id) {
        java.sql.Connection conn = null;
        java.sql.PreparedStatement stmt = null;

        try {
            // 获取数据库连接
            conn = getConnection(Config.JDBC_URL, Config.JDBC_USERNAME, Config.JDBC_PASSWORD);

            // 构建删除SQL语句
            String sql = "DELETE FROM employee WHERE id = ?";

            // 准备执行SQL
            stmt = conn.prepareStatement(sql);

            // 设置参数
            stmt.setInt(1, id);

            // 执行删除操作
            int rowsAffected = stmt.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println("员工信息删除成功！");
                return true;
            } else {
                System.out.println("员工信息删除失败！可能原因：员工不存在。");
                return false;
            }

        } catch (java.sql.SQLException e) {
            System.err.println("删除员工信息失败：" + e.getMessage());
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (java.sql.SQLException e) {
                System.err.println("关闭数据库连接失败：" + e.getMessage());
            }
        }
        return false;
    }
}
